package etc.com.sql;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class DBAdapter extends SQLiteOpenHelper {

	private static String dbName = "MarketApp";
	private static String tblStore = "tbl_Stores";
	private static String tblProducts = "tbl_Products";
	private static String tblStoreProducts = "tbl_Store_Product";
	private static String tblInfo = "tbl_Infomation";
	private static String tblInfoDetails = "tbl_Info_Details";
	
	public DBAdapter(Context context) {
		super(context, dbName, null, 1);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

		db.execSQL("DROP TABLE IF EXISTS " +  tblStoreProducts);
		db.execSQL("DROP TABLE IF EXISTS " + tblStore);
		db.execSQL("CREATE TABLE " + tblStore + "(id TEXT PRIMARY KEY, " +
					"STRNM TEXT, [ADD] TEXT, IMAGE TEXT, ADID TEXT)");
		db.execSQL("DROP TABLE IF EXISTS " + tblProducts);
		db.execSQL("CREATE TABLE " + tblProducts + "(id TEXT PRIMARY KEY, " +
				"PRDNM TEXT, SUBTITLE TEXT, CATEGORY CHAR, DSCRPT TEXT, VDCLIP TEXT, THUMBIMG TEXT, " +
				"UPPRDID TEXT, IMAGE TEXT)");
		db.execSQL("CREATE TABLE " + tblStoreProducts + "(id TEXT, PRDID TEXT)");
		db.execSQL("DROP TABLE IF EXISTS " + tblInfoDetails);
		db.execSQL("DROP TABLE IF EXISTS " + tblInfo);
		db.execSQL("CREATE TABLE " + tblInfo + " (id TEXT PRIMARY KEY, " +
				"INFOTITLE TEXT)");
		db.execSQL("CREATE TABLE " + tblInfoDetails + "(id TEXT, SEQ TEXT, CNTNT TEXT)");
	}
	
	public SQLiteDatabase getDB(){
		return this.getWritableDatabase();
	}
	
	public void insertStoreTable(String storeId, String storeName, String add, String image, String addId){
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL("INSERT INTO " + tblStore + " VALUES ('" + storeId + "', '" + storeName +  "', '" + add +  "', '" + image +
				"', '" + addId + "' )");
		db.close();
	}
	
	public void insertProductTable(String prdId, String prdName, String subTitle, String cat, String descript, String vdClip, String thumpImg, String upPrdID, String storeId, String image){
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL("INSERT INTO " + tblProducts + " VALUES ('" + prdId + "', '" + prdName +  "', '" + subTitle +  "', '" + cat +
				"', '" + descript + "', '" + vdClip + "', '" + thumpImg + "', '" + upPrdID + "', '" + image + "')");
		db.execSQL("INSERT INTO " + tblStoreProducts + " VALUES ('" + storeId + "', '" + prdId + "')");
		db.close();
	}
	
	public void insertInfomation(String id, String title, String seq, String cntnt){
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL("INSERT INTO " + tblInfo + " VALUES ('" + id + "', '" + title + "')");
		db.execSQL("INSERT INTO " + tblInfoDetails + " VALUES ('" + id + "', '" + seq + "', '" + cntnt + "')");
		db.close();
	}

	public Cursor getAllStore(){
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery("SELECT * FROM " + tblStore, new String[]{});
		return cursor;
	}
	
	public Cursor getSpecifyProduct(String storeId){
		Cursor cursor = null;
		try{
			SQLiteDatabase db = this.getReadableDatabase();
			String sql = "SELECT " + tblProducts + ".* " +
						"FROM " + tblProducts + " INNER JOIN " + tblStoreProducts + 
						" ON " + tblProducts + ".id = " + tblStoreProducts + ".PRDID " + 
						" WHERE " + tblProducts +".UPPRDID is not null AND " + tblProducts + ".UPPRDID <> '' ";
			if(storeId != null){ 
				sql = sql +	" AND " + tblStoreProducts +".id = '" + storeId + "'";
			}
			cursor = db.rawQuery(sql, new String[]{});
		}catch(SQLiteException e){
			e.printStackTrace();
		}
		return cursor;
	}
	
	public Cursor getProductImage(String prodId){
		Cursor cursor = null;
		try{
			SQLiteDatabase db = this.getReadableDatabase();
			String sql = "SELECT THUMBIMG FROM " + tblProducts + " WHERE id='" + prodId + "'";
			cursor = db.rawQuery(sql, new String[]{});
		}catch(SQLiteException e){
			e.printStackTrace();
		}
		return cursor;
	}
	
	public Cursor getProduct(String storeId){
		SQLiteDatabase db = this.getReadableDatabase();
		String sql = "SELECT " + tblProducts + ".* " +
				"FROM " + tblProducts + " INNER JOIN " + tblStoreProducts + 
				" ON " + tblProducts + ".id = " + tblStoreProducts + ".PRDID " + 
				" WHERE (" + tblProducts +".UPPRDID is null OR " + tblProducts + ".UPPRDID = '') ";
		if(storeId != null){ 
			sql = sql +	" AND " + tblStoreProducts +".id = '" + storeId + "'";
		}
		Cursor cursor = db.rawQuery(sql, new String[]{});
		return cursor;
	}
	
	public Cursor getStoreAdd(String storeId){
		SQLiteDatabase db = this.getReadableDatabase();
		String sql = "SELECT [ADD]" +
					"FROM " + tblStore + 
					"WHERE STRID = '" + storeId + "'";
		Cursor cursor = db.rawQuery(sql, new String[]{});
		return cursor;
	}
	
	public Cursor getProductDetailsById(String productId){
		SQLiteDatabase db = this.getReadableDatabase();
		String sql = "SELECT * FROM " + tblProducts + " WHERE id='" + productId + "'";
		Cursor cursor = db.rawQuery(sql, new String[]{});
		return cursor;
	}
	
	public Cursor getListInfo(){
		SQLiteDatabase db = this.getReadableDatabase();
		String sql = "SELECT * FROM " + tblInfo;
		Cursor cursor = db.rawQuery(sql, new String[]{});
		return cursor;
	}
	
	public Cursor getInfoTitle(String id){
		SQLiteDatabase db = this.getReadableDatabase();
		String sql = "SELECT * FROM " + tblInfo + " WHERE id = '" + id + "'";
		Cursor cursor = db.rawQuery(sql, new String[]{});
		return cursor;
	}
	
	public Cursor getInfoDetail(String id){
		SQLiteDatabase db = this.getReadableDatabase();
		String sql = "SELECT * FROM " + tblInfoDetails + " WHERE id = '" + id + "'";
		Cursor cursor = db.rawQuery(sql, new String[]{});
		return cursor;
	}
	
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		
	}
	
}
